package com.gitee.aurora.controller;

import com.gitee.aurora.entity.TabAjAqBaqsyqkdjb;
import com.gitee.aurora.service.BaqCheckService;
import com.gitee.aurora.utils.ResultVO;
import com.gitee.aurora.vo.DataCheckVo;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import java.util.List;

/**
 * author: jiangfuxiao
 * Date:2022/9/2 9:28
 */
@Controller
@ResponseBody
@RequestMapping("baqCheck")
public class BaqCheckController {


    @Resource(name = "baqCheckServiceImpl")
    private BaqCheckService baqService;


    @GetMapping("/queryTableData")
    public ResultVO queryTableData(DataCheckVo vo) {
       String sql = "SELECT\n" +
                "\ttzs.S_ID AS S_ID,\n" +
                "\ttzs.S_ID AS LYBID,\n" +
                "\t'' AS GLJQBH,\n" +
                "\taj.S_CASE_FULL_NAME AS AJMC,\n" +
                "\t'' AS DAFS,\n" +
                "\ttzs.DT_START_USE_TIME AS CHSJ,\n" +
                "\ttzs.DT_END_USE_TIME AS LKSJ,\n" +
                "\tCASE tzs.S_RULING_RESULT\n" +
                "WHEN '刑拘' THEN\n" +
                "\t'1'\n" +
                "WHEN '逮捕' THEN\n" +
                "\t'2'\n" +
                "WHEN '取保候审' THEN\n" +
                "\t'3'\n" +
                "WHEN '监居' THEN\n" +
                "\t'4'\n" +
                "WHEN '结束传唤' THEN\n" +
                "\t'6'\n" +
                "WHEN '罚款' THEN\n" +
                "\t'8'\n" +
                "WHEN '警告' THEN\n" +
                "\t'9'\n" +
                "WHEN '强制隔离戒毒' THEN\n" +
                "\t'10'\n" +
                "WHEN '社区戒毒' THEN\n" +
                "\t'11'\n" +
                "WHEN '其他' THEN\n" +
                "\t'12'\n" +
                "WHEN '其他' THEN\n" +
                "\t'12'\n" +
                "END AS LKYY,\n" +
                " '' AS WSBH,\n" +
                " tzs.S_ID AS DJB,\n" +
                " '' AS GLYQM1,\n" +
                " '' AS GLYQM2,\n" +
                " '' AS GLY1XM,\n" +
                " '' AS GLY2XM,\n" +
                " '' AS GLY1JH,\n" +
                " '' AS GLY2JH,\n" +
                " tzs.S_ENTER_POLICE AS DCMJXM,\n" +
                " tzs.S_ENTER_POLICE AS DCMJJH,\n" +
                " '' AS QTDAFS,\n" +
                " person.S_USER_ID AS RYBH,\n" +
                " person.S_LINK_CODE AS SHBH,\n" +
                " person.S_NAME AS XM,\n" +
                " CASE\n" +
                "WHEN person.S_CARD_TYPE = '无' THEN\n" +
                "\t0\n" +
                "WHEN person.S_CARD_TYPE = '身份证' THEN\n" +
                "\t1\n" +
                "WHEN person.S_CARD_TYPE = '驾驶证' THEN\n" +
                "\t2\n" +
                "WHEN person.S_CARD_TYPE = '军人证' THEN\n" +
                "\t3\n" +
                "WHEN person.S_CARD_TYPE = '学生证' THEN\n" +
                "\t4\n" +
                "WHEN person.S_CARD_TYPE = '护照' THEN\n" +
                "\t5\n" +
                "ELSE\n" +
                "\t99\n" +
                "END AS ZJLX,\n" +
                " person.S_CARD_NO AS ZJHM,\n" +
                " CASE person.I_SEX\n" +
                "WHEN 0 THEN\n" +
                "\t1\n" +
                "WHEN 1 THEN\n" +
                "\t0\n" +
                "ELSE\n" +
                "\t2\n" +
                "END AS XB,\n" +
                " person.S_BIRTHDAY AS CSRQ,\n" +
                " person.I_AGE AS NL,\n" +
                " '' AS GJDM,\n" +
                " '中国' AS GJ,\n" +
                " person.S_NATIONALTILY AS MZ,\n" +
                " person.S_PHONE AS LXFS,\n" +
                " person.S_DOMICILE_PLACE AS HJSZD,\n" +
                " person.S_CURRENT_RESIDERCE AS XZDXZ,\n" +
                " reg.I_INAREA_PERSON_TYPE AS RYLX,\n" +
                " CONCAT(\n" +
                "\t'{\"caption\":\"',\n" +
                "\tIFNULL(DICT.S_VALUE, ''),\n" +
                "\t'\"}'\n" +
                ") AS RQYY,\n" +
                " cr.S_CAPTION AS SAAY,\n" +
                " aj.S_CASE_CODE AS AJBH,\n" +
                " tzs.S_PLACE_ID AS SSCSBH,\n" +
                " cs.S_ORG_ID AS SSCSID,\n" +
                " cs.S_CAPTION AS SSCSMC,\n" +
                " cs.S_CAPTION AS SSBAZX,\n" +
                " org.S_CODE AS BADWBH,\n" +
                " org.S_NAME AS BADWMC,\n" +
                " ar.S_AGENT_ID AS BAMJJH,\n" +
                " SUBSTRING_INDEX(ar.S_AGENT, ',', 1) AS BAMJXM,\n" +
                " tzs.DT_START_USE_TIME AS RQSJ,\n" +
                " '' AS RYZPURL,\n" +
                " tzs.S_ENTER_POLICE AS DJYXM,\n" +
                " tzs.S_ENTER_POLICE AS DJYJH,\n" +
                " NOW() AS CJSJ,\n" +
                " p3.S_SELF_SYMPTOM AS ZSZZ,\n" +
                " p3.S_INJUERY_SITUATION AS JCQK,\n" +
                " '' AS ZSSFYJWBS,\n" +
                " '' AS ZSSFYJ,\n" +
                " '' AS ZSSFYCRB,\n" +
                " '' AS JCSFYSH,\n" +
                " '' AS JCSFYJ,\n" +
                " '' AS JCQSSFZC,\n" +
                "\n" +
                "IF (p1.I_COLLECTE = 1, 1, 0) AS SFXXCJ,\n" +
                " '' AS CJXM,\n" +
                " IFNULL(p1.I_WAREHOUSE, 0) AS XXRK,\n" +
                " IFNULL(p1.I_INSPECT_CONTRAST, 0) AS HCBD,\n" +
                " CASE WHEN p1.DT_START_TIME IS NULL AND p1.DT_END_TIME IS NULL THEN p1.DT_RECORD_TIME ELSE p1.DT_START_TIME END  XXCJKSSJ,\n" +
                " CASE WHEN p1.DT_START_TIME IS NULL AND p1.DT_END_TIME IS NULL THEN p1.DT_RECORD_TIME ELSE p1.DT_END_TIME END AS XXCJJSSJ,\n" +
                " '' AS ZMJCT,\n" +
                " '' AS BMJCT,\n" +
                " CASE WHEN p3.DT_START_TIME IS NULL AND p3.DT_END_TIME IS NULL THEN p3.DT_RECORD_TIME ELSE p3.DT_START_TIME END AS JCKSSJ,\n" +
                " CASE WHEN p3.DT_START_TIME IS NULL AND p3.DT_END_TIME IS NULL THEN p3.DT_RECORD_TIME ELSE p3.DT_END_TIME END AS JCJSSJ,\n" +
                " p1.S_USER_ID AS JCMJJH,\n" +
                " p1.S_USER_NAME AS JCMJXM,\n" +
                " CASE WHEN p2.DT_START_TIME IS NULL AND p2.DT_END_TIME IS NULL THEN p2.DT_RECORD_TIME ELSE p2.DT_START_TIME END AS XDJCKSSJ,\n" +
                " CASE WHEN p2.DT_START_TIME IS NULL AND p2.DT_END_TIME IS NULL THEN p2.DT_RECORD_TIME ELSE p2.DT_END_TIME END AS XDJCJSSJ,\n" +
                " p2.S_CHECK_PLACE AS XDJCDD,\n" +
                " '' AS BARYQZ,\n" +
                " '' AS XDWXWP,\n" +
                " '' AS SSCWGLYQM,\n" +
                " cs.S_ORG_ID AS SSFXJDM,\n" +
                " cs.S_CAPTION AS SSFJXMC,\n" +
                " SUBSTRING_INDEX(ar.S_AGENT, ',', -1) AS  BAMJ2XM,\n" +
                " SUBSTRING_INDEX(ar.S_AGENT, ',', 1) AS DRMJ1XM,\n" +
                " SUBSTRING_INDEX(ar.S_AGENT, ',', - 1) AS DRMJ2XM,\n" +
                "\n" +
                "IF (\n" +
                "\ttzs.I_DEL_FLG = 1,\n" +
                "\t1,\n" +
                "\t0\n" +
                ") AS SFSC,\n" +
                " now() AS TJSJ,\n" +
                " now() AS GXSJ,\n" +
                " 'B' AS XTLY\n" +
                "FROM\n" +
                "\tTAB_ZFZT_STANDBOOK tzs\n" +
                "LEFT JOIN TAB_ZFZT_PERSON_INFO person ON person.S_ID = tzs.S_PERSON_ID\n" +
                "LEFT JOIN TAB_FZ_CASE_INFO aj ON aj.S_CASE_CODE = CONVERT (\n" +
                "\ttzs.S_ARCHIVE_CODE USING utf8\n" +
                ")\n" +
                "AND aj.S_CASE_CODE <> ''\n" +
                "LEFT JOIN TAB_ZFZT_ARCHIVE ar ON ar.S_ARCHIVE_CODE = tzs.S_ARCHIVE_CODE\n" +
                "AND ar.S_ARCHIVE_CODE <> ''\n" +
                "LEFT JOIN TAB_ZHFZ_CASE_REASON cr ON cr.S_ID = aj.S_REASON_ID\n" +
                "LEFT JOIN TAB_FZ_PLACE_RESOURCE cs ON cs.S_ID = tzs.S_PLACE_ID\n" +
                "LEFT JOIN TAB_ORG org ON aj.S_COMPANY_ID = org.S_ID\n" +
                "LEFT JOIN TAB_ZFZT_PLACE_SUBPROCESS_AREA_REGISTER reg ON reg.S_ID = CONVERT (tzs.S_ID USING utf8)\n" +
                "LEFT JOIN TAB_ZFZT_PLACE_SUBPROCESS_INFO_COLLECT p1 ON p1.S_INAREA_REGISTER_ID = reg.S_ID\n" +
                "LEFT JOIN TAB_ZFZT_PLACE_SUBPROCESS_DRUG_CHECK p2 ON p2.S_INAREA_REGISTER_ID = reg.S_ID\n" +
                "LEFT JOIN TAB_ZFZT_PLACE_SUBPROCESS_SAFE_CHECK p3 ON p3.S_INAREA_REGISTER_ID = reg.S_ID\n" +
                "LEFT JOIN TAB_SETTING_DICTIONARY DICT ON DICT.S_TAG = 'S_TO_CASE_TYPE'\n" +
                "AND DICT.S_CODE = reg.I_TO_CASE_TYPE\n";
        vo.setSql(sql);
        return baqService.queryTableData(vo);
    }

}
